package com.yonyou.crm.common.base;

import java.io.*;
import java.lang.reflect.*;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import com.yonyou.crm.common.enumeration.entity.EnumVO;
import com.yonyou.crm.common.enumeration.rmitf.IEnumRmService;
import com.yonyou.crm.common.exception.CrmMessageException;
import com.yonyou.crm.common.rest.constant.AppCodeConstant;
import com.yonyou.crm.sys.user.entity.TypeEnum;
import com.yonyou.crm.sys.user.entity.UserVO;
import com.yonyou.crm.sys.user.rmitf.IUserRmService;

@RestController
@RequestMapping(value = "/test/excel")
public class ExcelImport {

	@Autowired
	IEnumRmService enumService;
	
	@Autowired
	private IUserRmService userService;
	
	/**
	 * @Title: createWorkbook
	 * @Description: 判断excel文件后缀名，生成不同的workbook
	 * @param is
	 * @param excelFileName
	 * @return Workbook
	 * @throws IOException
	 */
	public Workbook createWorkbook(InputStream is, String excelFileName) throws IOException {
		if (excelFileName.endsWith(".xls")) {
			return new HSSFWorkbook(is);
		} else if (excelFileName.endsWith(".xlsx")) {
			return new XSSFWorkbook(is);
		}
		return null;
	}

	/**
	 * @Title: getSheet
	 * @Description: 根据sheet索引号获取对应的sheet
	 * @param workbook
	 * @param sheetIndex
	 * @return Sheet
	 * @throws
	 */
	public Sheet getSheet(Workbook workbook, int sheetIndex) {
		return workbook.getSheetAt(0);
	}

	@RequestMapping(value = "import", method = RequestMethod.POST)
	public Object importExcel(HttpServletRequest request, HttpServletResponse response) throws IOException, CrmMessageException {
		
		UserVO vo = new UserVO();

		Map<String, Object> importParam = new HashMap<>();
		List<Map<String, Object>> fields = new ArrayList<>();
		getFieldList(fields);
		
		importParam.put("fields", fields);
		
		List<UserVO> list = importDataFromExcel(vo, request, importParam);
		
		return null;
	}
	
	/**
	 * 获取字段列表
	 * @param fields
	 */
	public void getFieldList(List<Map<String, Object>> fields) {
		//获取枚举值
		String[] codes = {"gender", "job"};
		Map<String, List<EnumVO>> enumList = enumService.getEnumByCodes(codes);
		
		Map<String, Object> field;
		String code;
		List<EnumVO> enumlist = new ArrayList<EnumVO>();
		/*
		field = new HashMap<>();
		field.put("name", "公司名称");
		field.put("field", "orgId");
		field.put("isRequired", "Y");
		field.put("type", "Long");
		fields.add(field);
		*/
		field = new HashMap<>();
		code = "name";
		field.put("name", "名称");
		field.put("field", "name");
		field.put("isRequired", "Y");
		field.put("type", "java.lang.String");
		fields.add(field);
		
		field = new HashMap<>();
		field.put("name", "邮箱");
		field.put("field", "email");
		field.put("isRequired", "Y");
		field.put("type", "java.lang.String");
		fields.add(field);
		
		field = new HashMap<>();
		field.put("name", "手机号");
		field.put("field", "phone");
		field.put("isRequired", "Y");
		field.put("type", "java.lang.String");
		fields.add(field);
		
		field = new HashMap<>();
		code = "gender";
		field.put("name", "性别");
		field.put("field", code);
		field.put("isRequired", "N");
		field.put("type", "java.lang.Integer");
		enumlist =  enumList.get(code);
		field.put("enumlist", enumlist);
		fields.add(field);
		
		field = new HashMap<>();
		code = "job";
		field.put("name", "职位");
		field.put("field", code);
		field.put("isRequired", "N");
		field.put("type", "java.lang.Integer");
		enumlist =  enumList.get(code);
		field.put("enumlist", enumlist);
		fields.add(field);
		
		
		field = new HashMap<>();
		code = "type";
		field.put("name", "类型");
		field.put("field", code);
		field.put("isRequired", "N");
		field.put("type", "java.lang.Integer");
		enumlist = new ArrayList<EnumVO>();
		
		for(TypeEnum typeEnum : TypeEnum.values()) {
			EnumVO enumValue = new EnumVO();
			enumValue.setName(typeEnum.getName());
			enumValue.setValue(typeEnum.getValue());
			enumlist.add(enumValue);
		}
		field.put("enumlist", enumlist);
		fields.add(field);
	}

	/**
	 * @Title: importDataFromExcel
	 * @Description: 将sheet中的数据保存到list中，
	 *               1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String
	 *               2、在action调用此方法时，需声明 private File excelFile;上传的文件 private
	 *               String excelFileName;原始文件的文件名 3、页面的file控件name需对应File的文件名
	 * @param vo javaBean
	 * @param is 输入流
	 * @return List<Object>
	 * @throws IOException, CrmMessageException
	 *             
	 */
	public <T extends Serializable> List<T> importDataFromExcel(T vo, HttpServletRequest request, Map<String, Object> importParam) throws CrmMessageException, IOException {
		MultipartHttpServletRequest re = (MultipartHttpServletRequest) request;
		MultipartFile fileM = re.getFile("uploadFile");
		String filename = fileM.getOriginalFilename();
		CommonsMultipartFile cf = (CommonsMultipartFile) fileM;
		InputStream is = cf.getInputStream();
	    List<T> list = new ArrayList<>();
		try {
			// 创建工作簿
			Workbook workbook = this.createWorkbook(is, filename);
			// 创建工作表sheet
			Sheet sheet = this.getSheet(workbook, 0);
			// 获取sheet中数据的行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 获取表头单元格个数
			int cells = sheet.getRow(0).getPhysicalNumberOfCells();
			// 获取导入列表项
			List<Map<String, Object>> fields = (List<Map<String, Object>>)importParam.get("fields");
			
			for (int i = 1; i < rows; i++) {
				// 第一行为标题栏，从第二行开始取数据
				Row row = sheet.getRow(i);
				int index = 0;
				while (index < cells) {
					Cell cell = row.getCell(index);
					if (null == cell) {
						cell = row.createCell(index);
					}
					
					Map<String, Object> field = fields.get(index);
					String paramClassName = String.valueOf(field.get("type"));
					
					Object value = null;
					switch (cell.getCellType()){
						case HSSFCell.CELL_TYPE_NUMERIC: // 数字   
							if (HSSFDateUtil.isCellDateFormatted(cell)){
								 value = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
							} else if (paramClassName.equals("java.lang.String")) {
								cell.setCellType(HSSFCell.CELL_TYPE_STRING);
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue();
							}
							break;
						case HSSFCell.CELL_TYPE_STRING: // 字符串   
							value = cell.getStringCellValue();
		                    break;   
		                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean   
		                	value = cell.getBooleanCellValue();   
		                    break;   
		                case HSSFCell.CELL_TYPE_FORMULA: // 公式   
		                	//value = cell.getCellFormula();   
		                    //break;
		                case HSSFCell.CELL_TYPE_BLANK: // 空值   
		                case HSSFCell.CELL_TYPE_ERROR: // 故障      
		                default:   
		                	value = "";   
		                    break;
					}

					if (field.containsKey("enumlist") && (field.get("enumlist") != null)) {
						//转化枚举值
						List<EnumVO> enumList = (List<EnumVO>)field.get("enumlist");
						for(EnumVO enumItem : enumList) {
							if (value.equals(enumItem.getName())) {
								value = enumItem.getValue();
							}
						}
					}

					String fieldName = String.valueOf(field.get("field"));
					String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
					//TODO动态生成方法
					Method setMethod = vo.getClass().getMethod(methodName, new Class[]{Class.forName(paramClassName)});
					setMethod.invoke(vo, new Object[]{value});
					index++;
				}

				if (isHasValues(vo)) {
					//TODO判断对象是否有值
					//判断对象属性是否有值 
					list.add(vo);
					vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象
				}
				
				//插入数据
				userService.insert((UserVO)vo);
			}
		} catch (Exception e) {
			throw new CrmMessageException(AppCodeConstant.ServerError, "获取Excel文件数据错误");
		} finally {
			try {
				is.close();// 关闭流
			} catch (Exception e2) {
				throw new CrmMessageException(AppCodeConstant.ServerError, "操作输入流错误");
			}
		}
		return list;
	}

	/**
	 * @throws CrmMessageException
	 * @Title: isHasValues
	 * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true
	 * @param @param object
	 * @param @return
	 * @return boolean
	 * @throws
	 */
	public boolean isHasValues(Object object) throws CrmMessageException {
		//获取类里所有方法
		Field[] fields = object.getClass().getDeclaredFields();
		boolean flag = false;
		for (int i = 0; i < fields.length; i++) {
			String fieldName = fields[i].getName();
			String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
			Method getMethod;
			try {
				getMethod = object.getClass().getMethod(methodName);
				Object obj = getMethod.invoke(object);
				if (null != obj && !("".equals(obj))) {
					flag = true;
					break;
				}
			} catch (Exception e) {
				throw new CrmMessageException(AppCodeConstant.ServerError, "属性错误");
			}
		}
		return flag;
	}
	
	/**
	 * 导入数据
	 * @param list
	 */
	public <T extends Serializable> void importData(List<T> list) {
		List<UserVO> List = (List<UserVO>)list;
		if (List != null && List.size() > 0) {
			for(UserVO vo : List) {
				userService.insert(vo);
			}
		}
	}

}
